Shareware Grab Bag/Shareware Grab Bag.iso/003/multidb.arc/MULTIDB.TXT

Text File | 1985-11-01 | 14.9 KB | 475 lines

October 25, 1985

TO:   All interested dBASE and Clipper users

FROM: K. E. Saffer

RE:   Multi-user file access


     It all started in 1983 while I was writing a financial
application for an interior design firm in Springfield, MA.  I
was using dBASE II on a Columbia MPC running MP/M-68.  We had 8
users on line and I had to come up with some sort of file access
control methods or the users would most certainly collide with
each other sooner or later.

     Being experienced with LAN systems I possessed a working
knowledge of how they control file access.  Either on disk or
located somewhere in shared memory is a structure called a
semaphore table.  Each record within this structure has space for
a data file name and in the case of record locking schemes a
record number.  There is also other specialized information that
varies from system to system, but we are only interested in the
basics.

     When a user application wishes to open a file in a
read/write mode, it would first check the semaphore table to
determine if the file and record were free to use.  If so, the
semaphore table was rapidly updated to show that the file was in
use (locked).  The changes were made to the file, any dirty
buffers were flushed, and the semaphore table was again updated
to show that the file was now free to be used (unlocked).

     Before we progress any further, some facts about dBASE data
file access must be known.  When you USE a data file, you are in
reality opening the file in read-write mode.  So long as you do
not use any commands that make changes to a data file (EDIT,
REINDEX, and the dreaded APPEND BLANK to name a few) the file may
be closed without writing any information the the disk drive.
This allows multiple users to scan and search though the file all
day long without doing any damage.

     Also, the actual amount of data brought into memory from a
file read is rarely a single record.  In reality, several records
are brought into memory, so say bye-bye to any hope of using
record locking techniques in this system (or at least until AT
comes out with a workable multi-user database manager).

     This leaves us with file locking as our only recourse.  As
the name implies, once a file is locked by an application, no
other application can make changes to the file until the original
application is through with it.  This requires that an
application store the changes to be made in local memory
variables, then quickly open the file, perform any REPLACEs and
close it.  If you have been in the habit of allowing a user to
keep a file open in USE all the time and are performing @..GETs
on the data file fields directly, you will have to make changes
in your programming style.

     I decided to implement a semaphore table on disk as a dBASE
database.  At first a single database was used to hold all
references to locked files, but one day that file became
corrupted due to two users trying to lock two different files at
the same time.  To correct this, I use a small database file as a
semaphore table for each database file that requires controlled
access.

     Enough of this! How do I use it ?!?!?!?

     OK, OK. I know this is boring, but your multi-user users are
gonna love you for this.  Here is a real-life example.  We have a
dBASE file called CUSTOMER.DBF, with it's associated index
CUSTOMER.NDX.  To provide control over the customer file we will
create a semaphore file called CUSTLOCK.DBF.  Its structure
consists of one logical field called LOCKED.  The CUSTLOCK data
file will have only one record in it, and the LOCKED field will
be initialized to .T.

     The program that manipulates the customer file has three
separate sections in it: viewing, editing, and adding records.
When the application is viewing the file, the semaphore file is
ignored because the view section will never make any changes to
it.  When editing or adding records, the program will first check
the semaphore file to make sure no one else is making changes.
If the file is locked, the operator is prompted to wait until
it becomes available, otherwise the file is locked and changes
made.  Then the program will close the customer file, update the
semaphore file and proceed about it's business.

     The trick here is to read the data file information into
local memory variables, perform editing on these, then REPLACE
the data file fields with the memory variables.  This gives the
program the opportunity of knowing exactly when it is going to
make any changes to the file.  We shall maintain control.
Make darn sure your program never changes a file without your
permission, or you will most certainly corrupt the file and
possibly lose your job.

     The following is an extract of a customer data file
manipulation program I wrote for an advanced point-of-sale
application used by a local retailer.  The system is compiled
using Clipper, but all Clipper-specific code has been removed or
rewritten to allow operation under dBASE III.  Remember that in
addition to the data manipulation programs, you must provide a
program that will unlock all semaphore files in case the system
is accidentally interrupted (i.e. power failure, lamebrain user,
etc.)



The structures of the files follow:

File CUSTOMER.DBF
Number of records:   30
Fieldname  Type Length Decimals
---------- ---- ------ --------
FIRST_NAME  C       20        0
INITIAL     C        2        0
LAST_NAME   C       20        0
ADDRESS1    C       40        0
ADDRESS2    C       40        0
ADDRESS3    C       40        0
ZIP_CODE    C       10        0

File Customer is indexed on UPPER(LAST_NAME) to CUSTOMER.NDX.


File CUSTLOCK.DBF
Number of records:    1
Fieldname  Type Length Decimals
---------- ---- ------ --------
LOCKED      L        1        0


The file manipulation program follows:

* Program ------:CUSTMAIN.PRG
* Author -------:Kevin E. Saffer
* Date ---------:October 28, 1985
* Notes --------:Demonstrate methods for multi-users file access control
*
*    This program has been freely placed into the public domain without
*    any restrictions concerning its use.

* establish working environment
SET BELL OFF
SET COLOR TO +7/0,7/0,0
SET DELETED ON
SET DELIMITER ON
SET DELIMITER TO '[]'
SET EXACT OFF
SET HEADINGS OFF
SET SAFETY OFF
SET TALK OFF

* Open the customer file and display heading
SELECT A
USE Customer.dbf INDEX Customer.ndx
CLEAR
@ 01,00 SAY "Customer Maintenance"
@ 01,67 SAY "Date " + DTOC(DATE())
@ 02,00 SAY "========================================" + ;
            "========================================"
* start main loop
DO WHILE .T.

  SET COLOR TO 7/0,+7/0

  * read file fields into local memvars
  Mfirst = First_name
  Minit = Initial
  Mlast = Last_name
  Maddr1 = Address1
  Maddr2 = Address2
  Maddr3 = Address3
  Mzip = Zip_code

  * get the record number for use later
  Mrec_no = RECNO()

  * display memvars
  @ 04,00 SAY " Lastname" GET Mlast
  @ 04,59 SAY "Record number [" + STR(Mrec_no,5,0) + "]"
  @ 05,00 SAY "Firstname" GET Mfirst
  @ 06,00 SAY "  Initial" GET Minit
  @ 08,00 SAY "  Address" GET Maddr1
  @ 09,00 SAY "         " GET Maddr2
  @ 10,00 SAY "         " GET Maddr3
  @ 11,00 SAY " Zip Code" GET Mzip
  CLEAR GETS

  * prompt operator
  Choice = " "
  SET CONFIRM OFF
  DO WHILE AT(Choice,"PNAEDX") = 0
    Choice = " "
    @ 23,00
    @ 23,00 SAY "P)revious, N)ext, A)dd, E)dit, D)elete or eXit?" ;
    GET Choice PICTURE '!'
    READ
  ENDDO

  DO CASE
  CASE Choice = "X"
    * exit program
    CLEAR
    QUIT

  CASE Choice = "P"
    * previous record
    SKIP -1
    IF BOF()
      @ 23,00
      @ 23,00 SAY "Beginning of file encountered, going to bottom..."
      GO BOTTOM
      LOOP
    ELSE
      LOOP
    ENDIF

  CASE Choice = "N"

    * next record
    SKIP
    IF EOF()
      @ 23,00
      @ 23,00 SAY "End of file encountered, going to top..."
      GO TOP
      LOOP
    ELSE
      LOOP
    ENDIF

  CASE Choice = "A"
    * add a new record

    * first, initialize memvars for operator input
    Mlast = "                    "
    Mfirst = "                    "
    Minit = "  "
    Maddr1 = "                                        "
    Maddr2 = "                                        "
    Maddr3 = "                                        "
    Mzip = "          "

    * allow operator to edit the memvars
    Edit_more = " "
    DO WHILE Edit_more <> "Y"
      Edit_more = " "
      @ 04,00 SAY " Lastname" GET Mlast
      @ 05,00 SAY "Firstname" GET Mfirst
      @ 06,00 SAY "  Initial" GET Minit
      @ 08,00 SAY "  Address" GET Maddr1
      @ 09,00 SAY "         " GET Maddr2
      @ 10,00 SAY "         " GET Maddr3
      @ 11,00 SAY " Zip Code" GET Mzip
      @ 23,00
      @ 23,00 SAY "Please enter the customer information."
      READ
      @ 23,00
      @ 23,00 SAY "Is the above information correct? (Y/N)" ;
      GET Edit_more PICTURE '!'
      READ
    ENDDO

    * prompt operator to add or exit
    Add_it = " "
    @ 23,00
    @ 23,00 SAY "A)dd this new record or E)xit?" GET Add_it PICTURE '!'
    READ
    IF Add_it <> "A"
      * go back to display routine
      LOOP
    ENDIF

    * lock up customer file, we are still in work area A
    @ 23,00

    @ 23,00 SAY "Adding new record..."

    USE Custlock.dbf
    IF Locked
      SET COLOR TO *15/0,+7/0
      @ 23,00
      @ 23,00 SAY "Customer file in use, retrying..."
      SET COLOR TO 7/0,+7/0
    ENDIF
    DO WHILE Locked
      USE
      USE Custlock.dbf
    ENDDO
    REPLACE Locked WITH .T.
    USE

    * the semaphore database now shows the customer file in use, perform
    * replacements

    USE Customer.dbf INDEX Customer.ndx
    APPEND BLANK
    REPLACE First_name WITH Mfirst
    REPLACE Last_name WITH Mlast
    REPLACE Initial WITH Minit
    REPLACE Address1 WITH Maddr1
    REPLACE Address2 WITH Maddr2
    REPLACE Address3 WITH Maddr3
    REPLACE Zip_code WITH Mzip
    Mrec_no = RECNO()
    USE

    * now, unlock the customer file and get back to the record we just added
    USE Custlock.dbf
    REPLACE Locked WITH .F.
    USE Customer.dbf INDEX Customer.ndx
    GO Mrec_no
    LOOP

  CASE Choice = "E"
    * editing the current record is just like adding, without initializing
    * the memvars

    * allow operator to edit the memvars
    Edit_more = " "
    DO WHILE Edit_more <> "Y"
      Edit_more = " "
      @ 04,00 SAY " Lastname" GET Mlast
      @ 05,00 SAY "Firstname" GET Mfirst
      @ 06,00 SAY "  Initial" GET Minit
      @ 08,00 SAY "  Address" GET Maddr1
      @ 09,00 SAY "         " GET Maddr2
      @ 10,00 SAY "         " GET Maddr3
      @ 11,00 SAY " Zip Code" GET Mzip
      @ 23,00
      @ 23,00 SAY "Please enter any corrections."

      READ
      @ 23,00
      @ 23,00 SAY "Is the above information correct? (Y/N)" ;
      GET Edit_more PICTURE '!'
      READ
    ENDDO

    * prompt operator to add or exit
    Add_it = " "
    @ 23,00
    @ 23,00 SAY "A)dd these changes or E)xit?" GET Add_it PICTURE '!'
    READ
    IF Add_it <> "A"
      * go back to display routine
      LOOP
    ENDIF

    * lock up customer file, we are still in work area A
    @ 23,00
    @ 23,00 SAY "Adding these changes..."
    USE Custlock.dbf
    IF Locked
      SET COLOR TO *15/0,+7/0
      @ 23,00 SAY "                                           "
      @ 23,00 SAY "Customer file in use, retrying..."
      SET COLOR TO 7/0,+7/0
    ENDIF
    DO WHILE Locked
      USE
      USE Custlock.dbf
    ENDDO
    REPLACE Locked WITH .T.
    USE

    * the semaphore database now shows the customer file in use, perform
    * replacements
    USE Customer.dbf INDEX Customer.ndx

    * get back to the record we want
    GO Mrec_no
    REPLACE First_name WITH Mfirst
    REPLACE Last_name WITH Mlast
    REPLACE Initial WITH Minit
    REPLACE Address1 WITH Maddr1
    REPLACE Address2 WITH Maddr2
    REPLACE Address3 WITH Maddr3
    REPLACE Zip_code WITH Mzip
    Mrec_no = RECNO()
    USE

    * now, unlock the customer file and get back to the record we just edited
    USE Custlock.dbf
    REPLACE Locked WITH .F.
    USE Customer.dbf INDEX Customer.ndx
    GO Mrec_no

    LOOP

  CASE Choice = "D"
    * delete record
    Kill_it = " "
    @ 23,00
    @ 23,01 SAY "ARE YOU SURE YOU WANT THIS CUSTOMER DELETED? (Y/N)" ;
    GET Kill_it PICTURE '!'
    READ
    IF Kill_it = "Y"
      @ 23,00
      @ 23,01 SAY "Deleting this customer..."
      Oldrec = RECNO()
      USE Custlock.dbf
      IF Locked
        SET COLOR TO *15/0,+7/0
        @ 23,00
        @ 23,01 SAY "Customer file in use, retrying..."
        SET COLOR TO 7/0,+7/0
      ENDIF
      DO WHILE Locked
        USE
        USE Custlock.dbf
      ENDDO
      REPLACE Locked WITH .T.
      USE
      USE Customer.dbf INDEX Customer.ndx
      GO Mrec_no

      * check to make sure no one else has deleted this record while we were
      * locking up the file
      IF Last_name = Mlast .AND. First_name = Mfirst
        DELETE
        USE
        USE Custlock.dbf
        REPLACE Locked WITH .F.
        USE
      ENDIF
      USE Customer.dbf INDEX Customer.ndx
      GO TOP
      @ 23,00 CLEAR
    ENDIF
  ENDCASE
ENDDO

* end of file CUSTMAIN.PRG



     The actual program in use has search and print capabilities,
a function to unlock the semaphore file, and an indexing routine.
The indexing routine is important because the program does not
pack the data file when a record is deleted.  The operator thinks
it's gone because we SET DELETED ON in the beginning of the
program.  Therefore, once a week the file is re-indexed to remove

any deleted records.

     The program runs relatively slowly in interpetive dBASE, but
thats what the Clipper compiler is for.  The compiled application
goes like lightning.

     The advantage to having the program take care of the file
locking methods is that now the application is not "LAN
specific".  It can run on nearly any LAN or multi-user system, so
long as the system manager give everyone read-write access to the
volume where the semaphore files reside.

     While you are experimenting with the program, see what
happens when you try to edit the file when the semaphore database
shows the file is locked.  Currently, I make the user wait a
second or two until the file becomes available.  You might give
the operator a choice of whether to wait for the file, or exit.

     The program by itself is only a demonstration of how
controlled file sharing may be accomplished and is nowhere near a
complete database manipulation program.  I leave it up to you to
make it perfect.


Thank you for your support.

KES